package database;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;

import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.log4j.Logger;

import data.WoTCWChannel;

import main.TankSicherheit;
import utils.Utils;
import web.interfaces.ClanwarLineupReceiverDB;
import web.interfaces.GoldCaretakerDB;

public class Database implements GoldCaretakerDB, ClanwarLineupReceiverDB{
	private String dbadmin = "ofwog4ot4wtsf";
	private String dbadminpw = "10rh2i4gfwgfwef";

	public String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	public String protocol = "jdbc:derby:";

	private String dbname = "TankSicherheit";

	private Connection conn = null;
	public static org.apache.log4j.Logger LOGGER =null;
	
	public static final int LOGIN_FAILED_INVALID_LOGIN_CREDENTIALS = -5;
	public static final int LOGIN_FAILED_EMPTY_RESULTSET = -6;

	public Database(boolean createNew) {
		LOGGER = Logger.getLogger(Database.class);
		Properties props = new Properties();
		props.put("user", dbadmin);
		props.put("password", dbadminpw);
		System.setProperty("derby.system.home", TankSicherheit.tasihomepath+ "derby");
		TankSicherheit.LOGGER.info("derby.system.home: " +TankSicherheit.tasihomepath + "derby");
		try 
		{
			Class.forName(driver).newInstance();
			conn = DriverManager.getConnection(protocol + dbname + ";"
					+ (createNew ? "create=true" : ""), props);
			conn.setAutoCommit(false);
			
			
			if (createNew) 
			{
				createDatabaseSchema();
			}
			prepareStatements();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void createDatabaseSchema() {
		try 
		{
			InputStream input = this.getClass().getResourceAsStream("dbsetup.sql");
			String sql = Utils.convertStreamToString(input);
			Statement s = conn.createStatement();
			for (String ss : sql.split(";")) 
			{
				System.out.println(ss);
				s.execute(ss);
			}
			conn.commit();
			s.close();
		} catch (Exception e) 
		{
			e.printStackTrace();
		}

	}

	public void shutdown() 
	{
		System.out.println("shutting down database");
		if (conn != null) 
		{
			try 
			{
				conn.commit();
				conn.close();
				DriverManager.getConnection("jdbc:derby:;shutdown=true");
			} catch (Exception e) 
			{
				if(!e.getMessage().equals("Derby system shutdown."))
				{
					e.printStackTrace();
				}
				
			}
		}
	}

	public static void main(String[] args) {
		new Database(true).shutdown();
	}
	
	
	public void prepareStatements()
	{
		try
		{
			findPendingTransactionsStatement = conn.prepareStatement("SELECT * FROM TANKSICHERHEIT.PENDINGTRANSACTIONS");
			deleteFromPendingTransactionsStatement = conn.prepareStatement("SELECT * FROM TANKSICHERHEIT.PENDINGTRANSACTIONS WHERE ID=?");
			insertIntoFinishedTransactions = conn.prepareStatement("INSERT INTO TANKSICHERHEIT.FINISHEDTRANSACTIONS " +
					"(REMITTEE, PAYER, AMOUNT, REASON, REASONID)" +
					"VALUES (?,?,?,?,?)");
			insertWoTCWChannel = conn.prepareStatement("INSERT INTO TANKSICHERHEIT.WOTCWCHANNEL" +
					"(CHANNELID, MAP, ENEMY, FC)" +
					"VALUES (?,?,?,?)");
			insertCWTransaction = conn.prepareStatement("INSERT INTO TANKSICHERHEIT.PENDINGTRANSACTIONS" +
					"(REMITTEE, PAYER, AMOUNT, REASON, REASONID)" +
					"VALUES (?,?,?,?,?)");
			findUserIDByWoTIDStatement = conn.prepareStatement("SELECT * FROM TANKSICHERHEIT.USERS WHERE WOTACCOUNTID = ?");
			insertRawUserByWoTIDStatement = conn.prepareStatement("INSERT INTO TANKSICHERHEIT.USERS" +
					"(WOTACCOUNTID)" +
					"VALUES (?)");
			findUserByWoTNameStatement = conn.prepareStatement("SELECT * FROM TANKSICHERHEIT.USERS WHERE WOTACCOUNTNAME = ?");
			insertUserFromFormStatement = conn.prepareStatement("INSERT INTO TANKSICHERHEIT.USERS " +
					"(WOTACCOUNTNAME, PASSWORD, WOTACCOUNTID)" +
					"VALUES (?,?,?)");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	
	private PreparedStatement insertUserFromFormStatement = null;
	public int createUserFromForm(String username, String password, int wotaccountid)
	{
		TankSicherheit.LOGGER.debug("Database creating user from form: "+username);
		try
		{
			ResultSet rs = findUserByWoTName(username);
			if(!rs.next())
			{
				insertUserFromFormStatement.setString(1, username);
				insertUserFromFormStatement.setString(2, password);
				insertUserFromFormStatement.setInt(3,wotaccountid);
				return insertUserFromFormStatement.executeUpdate();
			}
			else
			{
				TankSicherheit.LOGGER.info("user allready existed!");
				return -3;
			}

		}
		catch(Exception e)
		{
			LOGGER.error("Exception during user-creation: "+username+" "+e);
		}
		
		
		
		return -1;
	}
	
	
	private PreparedStatement findUserByWoTNameStatement=null;
	public ResultSet findUserByWoTName(String name)
	{
		LOGGER.debug("Database searching user by WoTName");
		try
		{
			findUserByWoTNameStatement.setString(1, name);
			ResultSet user = findUserByWoTNameStatement.executeQuery();
			return user;
		}
		catch(Exception e)
		{
			LOGGER.error("Exception in findUserByWoTNameStatement: "+e);
		}
	
		
		return null;
	}
	
	
	private PreparedStatement findPendingTransactionsStatement=null;
	@Override
	public ResultSet getPendingTransactions() {
		try
		{
			return findPendingTransactionsStatement.executeQuery();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return null;
	}
	private PreparedStatement deleteFromPendingTransactionsStatement=null;
	@Override
	public int deleteFromPendingTransactions(int id)
	{
		try
		{
			deleteFromPendingTransactionsStatement.setInt(1, id);
			deleteFromPendingTransactionsStatement.executeUpdate();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return -1;
	}
	private PreparedStatement insertIntoFinishedTransactions=null;
	@Override
	public int insertIntoFinishedTransactions(int remittee, int payer, int amount, int reason, int reasonid) 
	{
		try
		{
			insertIntoFinishedTransactions.setInt(1, remittee);
			insertIntoFinishedTransactions.setInt(2, payer);
			insertIntoFinishedTransactions.setInt(3, amount);
			insertIntoFinishedTransactions.setInt(4, reason);
			insertIntoFinishedTransactions.setInt(5, reasonid);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return -1;
	}

	
	
	private PreparedStatement insertWoTCWChannel=null;
	@Override
	public boolean insertClanwar(int fc,WoTCWChannel chan, List<Integer> lineup, int lineupamount, List<Integer> roster, int rosteramound) {
		Savepoint save = null;
		try
		{
			save = conn.setSavepoint();
			insertWoTCWChannel.setInt(1, chan.channelid);
			insertWoTCWChannel.setString(2, chan.map);
			insertWoTCWChannel.setString(3, chan.enemy);
			insertWoTCWChannel.setInt(4, fc);
			insertWoTCWChannel.executeUpdate();
			conn.commit();
			
			for(int i:lineup)
			{
				int userid = findOrCreateUserIDByWoTID(i);
				insertPendingTransaction(userid,1,lineupamount,1,chan.channelid);
			}
			for(int i:roster)
			{
				int userid = findOrCreateUserIDByWoTID(i);
				if(userid>=0)
				{
					LOGGER.trace("scheduling "+rosteramound+" gold for userid="+userid);
					insertPendingTransaction(userid,1,lineupamount,1,chan.channelid);
				}
				else
				{
					LOGGER.warn("not scheduling any gold to userid="+userid);
				}
			}
			return true;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			try
			{
				conn.rollback(save);
			}
			catch(Exception ee)
			{
				e.printStackTrace();
			}
			
		}
		return false;
	}
	private PreparedStatement insertCWTransaction=null;
	private void insertPendingTransaction(int remittee, int payer, int amount, int reason,int reasonid)
	{
		try
		{
			insertCWTransaction.setInt(1, remittee);
			insertCWTransaction.setInt(2, payer);
			insertCWTransaction.setInt(3, amount);
			insertCWTransaction.setInt(4, reason);
			insertCWTransaction.setInt(5,reasonid);
			insertCWTransaction.executeUpdate();
			conn.commit();
		}
		catch(Exception e)
		{
			LOGGER.error("could not insert PendingTransaction: "+e+"\r\n"+ExceptionUtils.getStackTrace(e));
		}

	}
	private PreparedStatement findUserIDByWoTIDStatement=null;
	private PreparedStatement insertRawUserByWoTIDStatement=null;
	private int findOrCreateUserIDByWoTID(int id)
	{
		try
		{
			findUserIDByWoTIDStatement.setInt(1, id);
			ResultSet rs = findUserIDByWoTIDStatement.executeQuery();
			if(rs.next())
			{
				return rs.getInt("ID");
			}
			else
			{
				insertRawUserByWoTIDStatement.setInt(1, id);
				insertRawUserByWoTIDStatement.execute();
				return findOrCreateUserIDByWoTID(id);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return -1;
	}
}
